<?php
/**
 * Created by Zhy
 * User: Zhy
 * Date: 2020/2/20
 * Time: 16:46
 */

namespace app\admin\controller;


use think\Db;
use think\Request;
class Member extends Base
{
    /**
     * 用户列表
     * @return mixed|\think\response\Json
     */
    public function index(){
        if(request()->isAjax()){
            $where['user_status'] = ['neq', 3];
            $where['user_type'] = 1;
            if(session('groupid') == 2){
                $where['user_piano'] = session('piano');
            }
            $keyword = input('keyword');
            $start = input('res_start');
            $end = input('res_end');
            $start2 = input('login_start');
            $end2 = input('login_end');
            $user_school = input('user_school');
            $sex = input('sex');
            $utype = input('utype');
            $age = input('age');
            if(isset($keyword) && $keyword != ''){
                $where['user_id|user_username|user_phone'] = ['like', '%' . $keyword . '%'];
            }
            
            if(isset($start) && $start != "" && isset($end) && $end == "")
            {
                $where['user_time'] = ['>=',$start];
            }
            if(isset($end) && $end != "" && isset($start) && $start == "")
            {
                $where['user_time'] = ['<=',$end];
            }
            if(isset($start) && $start != "" && isset($end) && $end != "")
            {
                $where['user_time'] = ['between',[$start,$end]];
            }

            if(isset($start2) && $start2 != "" && isset($end2) && $end2 == "")
            {
                $where['user_last_login_time'] = ['>=',$start2];
            }
            if(isset($end2) && $end2 != "" && isset($start2) && $start2 == "")
            {
                $where['user_last_login_time'] = ['<=',$end2];
            }
            if(isset($start2) && $start2 != "" && isset($end2) && $end2 != "")
            {
                $where['user_last_login_time'] = ['between',[$start2,$end2]];
            }
            //琴行
            if(isset($user_school) && $user_school != "")
            {
                $where['user_piano'] = ['like','%'.$user_school.'%'];
            }
            //性别
            if(isset($sex) && $sex != "")
            {
                $where['user_gender'] = $sex;
            }
            //身份
            if(isset($utype) && $utype != "")
            {
                $where['user_type'] = $utype;
            }
            //年龄
            if(isset($age) && $age != "" && (int)$age > 1)
            {
                $age = (int)$age;
                $now = date('Y') - $age;
                $agedate1 = $now . '-01-01';
                $agedate2 = $now . '-12-31';
                $where['user_birthday'] = ['between',[$agedate1,$agedate2]];;
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('u_user')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::name('u_user')
                ->where($where)
                ->page($NowPage,$limits)
                ->order('user_time desc')
                ->select();
            foreach ($lists as $key => $vo){
                $lists[$key]['axes'] = '';
                $lists[$key]['panios'] = '';
                $yueqi = Db::table('up_axe')->where('axe_id','in',trim($vo['user_like_axe'],','))->column('axe_name');
                if($yueqi){
                    $lists[$key]['axes'] = implode($yueqi,',');
                }
                $panios = Db::table('up_piano')->where('pa_id','in',trim($vo['user_piano'],','))->column('pa_name');
                if($panios){
                    $lists[$key]['panios'] = implode($panios,',');
                }
            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $panio = Db::table('up_piano')
            ->select();
        if(session('groupid') == 2){
            $panio = Db::table('up_piano')
                ->where('pa_id',session('piano'))
                ->select();
        }
        $this->assign([
            'panio' => $panio,
        ]);
        return $this->fetch();
    }
    
    /**
     * 冻结
     * @return \think\response\Json
     */
    public function down(){
        $user_id = input('id');
        $res = Db::table('up_u_user')->where('user_id', $user_id)->setField('user_status', 2);
        if($res){
            return json(['code' => 200, 'msg' => '操作成功']);
        }else{
            return json(['code' => 100, 'msg' => '操作失败']);
        }
    }
    
    /**
     * 启用
     * @return \think\response\Json
     */
    public function up(){
        $user_id = input('id');
        $res = Db::table('up_u_user')->where('user_id', $user_id)->setField('user_status', 1);
        if($res){
            return json(['code' => 200, 'msg' => '操作成功']);
        }else{
            return json(['code' => 100, 'msg' => '操作失败']);
        }
    }
    /**
     * 删除
     * @return \think\response\Json
     */
    public function deluser(){
        $user_id = input('id');
        $res = Db::table('up_u_user')->where('user_id', $user_id)->setField('user_status', 3);
        if($res){
            return json(['code' => 200, 'msg' => '操作成功']);
        }else{
            return json(['code' => 100, 'msg' => '操作失败']);
        }
    }
    
    /**
     * 编辑
     * @return mixed
     */
    public function memberEdit(){
        $user_id = input('user_id');
        $info = Db::name('u_user')
            ->where('user_id', $user_id)
            ->find();
        if(Request::instance()->isPost()){
            $user_image = input('user_image');
            $user_username = input('user_username');
            $user_phone = input('user_phone');
            $user_password = input('user_password');
            $user_gender = input('user_gender');
            $user_birthday = input('user_birthday');
            $user_sign = input('user_sign');
            $user_like_axe = input('user_like_axe');
            $user_piano = input('user_piano');
            if(!preg_match("/^1[123456789]\d{9}$/", $user_phone)){
                return json(['code' => 100, 'msg' => '请输入正确的手机号']);
            }
            $update = [
                'user_image' => $user_image,
                'user_username' => $user_username,
                'user_phone' => $user_phone,
                'user_gender' => $user_gender,
                'user_birthday' => $user_birthday,
                'user_sign' => $user_sign,
                'user_like_axe' => $user_like_axe,
                'user_piano' => $user_piano,
                'user_update_time' => date('Y-m-d H:i:s')
            ];
            if(isset($_POST['user_other_axe']) && $_POST['user_other_axe']){
                $update['user_other_axe'] = $_POST['user_other_axe'];
            }
            if($user_password){
                $update['user_password'] = md5($user_password);
            }
            if($info['user_phone'] != $user_phone){
                $have1 = Db::name('u_user')
                    ->where('user_phone',$user_phone)
                    ->find();
                if($have1){
                    return json(['code' => 100, 'msg' => '已经有此手机号了']);
                }
            }
            $res = Db::name('u_user')->where('user_id',$user_id)->update($update);
            if($res){
                return json(['code' => 200, 'msg' => '编辑成功']);
            }else{
                return json(['code' => 100, 'msg' => '编辑失败']);
            }
        }
        $info['user_like_axe'] = explode(',',trim($info['user_like_axe'],','));
        $info['user_piano'] = explode(',',trim($info['user_piano'],','));
        $panio = Db::table('up_piano')->select();
        $axe = Db::table('up_axe')->select();
        $this->assign('info', $info);
        $this->assign('user_id', $user_id);
        $this->assign('panio', $panio);
        $this->assign('axe', $axe);
        return $this->fetch('member/memberdetail');
    }

    //----------------------5个详情----------------------------

    public function lists(){
        $user_id = input('user_id');
        if(request()->isAjax()){
            $mypiano = Db::table('up_u_user')->where('user_id',$user_id)->value('user_piano');
            if(empty($mypiano)){
                $mypiano = 'apicloud';
            }
            $where = [
                'l_id' => ['>',0]
            ];
            $keyword = input('keyword');
            if(isset($keyword) && $keyword != ''){
                $where['l_title'] = ['like', '%' . $keyword . '%'];
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::table('up_list')
                ->where(function ($query) use ($user_id,$where) {
                    $query->where($where)->where('l_type', 1)->where('l_student', $user_id);
                })
                ->whereOr(function ($query) use ($mypiano,$where) {
                    $query->where($where)->where('l_type', 2)->where('l_piano', 'like',"%$mypiano%");
                })
                ->count();// 获取总条数
            $lists = Db::table('up_list')
                ->where(function ($query) use ($user_id,$where) {
                    $query->where($where)->where('l_type', 1)->where('l_student', $user_id);
                })
                ->whereOr(function ($query) use ($mypiano,$where) {
                    $query->where($where)->where('l_type', 2)->where('l_piano', 'like',"%$mypiano%");
                })
                ->field('l_content',true)
                ->order('l_status asc,l_time DESC')
                ->page($NowPage,$limits)
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $this->assign('user_id',$user_id);
        return $this->fetch();
    }
    public function list_info(){
        $l_id = input('l_id');
        $data = Db::table('up_list')->where('l_id', $l_id)->find();
        $this->assign('data',$data);
        return $this->fetch();
    }

    /**
     * 课程进度
     * @return mixed|\think\response\Json
     */
    public function plans(){
        $user_id = input('user_id');
        if(request()->isAjax()){
            $where = [
                'p_status' => ['<>',3],
                'p_student' => $user_id
            ];
            $keyword = input('keyword');
            if(isset($keyword) && $keyword != ''){
                $where['p_name'] = ['like', '%' . $keyword . '%'];
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::table('up_plan')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::table('up_plan')
                ->where($where)
                ->order('p_status asc,p_time DESC')
                ->page($NowPage,$limits)
                ->select();
            if($lists){
                foreach ($lists as $k => $v){
                    $precent = Db::table('up_plan_info')
                        ->where('plan_p_id',$v['p_id'])
                        ->order('plan_percent desc')
                        ->value('plan_percent');
                    $lists[$k]['precent'] = $precent;
                }
            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $this->assign('user_id',$user_id);
        return $this->fetch();
    }
    /**
     * 给老师的评价
     * @return mixed|\think\response\Json
     */
    public function comments(){
        $user_id = input('user_id');
        if(request()->isAjax()){
            $where = [
                'plan_online' => ['<>',3],
                'plan_student' => $user_id
            ];
            $keyword = input('keyword');
            if(isset($keyword) && $keyword != ''){
                $where['user_username'] = ['like', '%' . $keyword . '%'];
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $field = 'p.*,user_username,user_phone,p_name';
            $count =  Db::table('up_plan_info')->alias('p')
                ->join('up_u_user u','p.plan_teacher=u.user_id','LEFT')
                ->join('up_plan pl','p.plan_p_id=pl.p_id','LEFT')
                ->where($where)
                ->count();// 获取总条数
            $lists =  Db::table('up_plan_info')->alias('p')
                ->join('up_u_user u','p.plan_teacher=u.user_id','LEFT')
                ->join('up_plan pl','p.plan_p_id=pl.p_id','LEFT')
                ->where($where)
                ->order('plan_online asc,plan_comment DESC')
                ->page($NowPage,$limits)
                ->field($field)
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $this->assign('user_id',$user_id);
        return $this->fetch();
    }
    /**
     * 上课记录
     * @return mixed|\think\response\Json
     */
    public function planinfos(){
        $user_id = input('user_id');
        if(request()->isAjax()){
            $where = [
                'plan_online' => ['<>',3],
                'plan_student' => $user_id
            ];
            $keyword = input('keyword');
            if(isset($keyword) && $keyword != ''){
                $where['user_username'] = ['like', '%' . $keyword . '%'];
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $field = 'p.*,user_username,user_phone,p_name';
            $count =  Db::table('up_plan_info')->alias('p')
                ->join('up_u_user u','p.plan_teacher=u.user_id','LEFT')
                ->join('up_plan pl','p.plan_p_id=pl.p_id','LEFT')
                ->where($where)
                ->count();// 获取总条数
            $lists =  Db::table('up_plan_info')->alias('p')
                ->join('up_u_user u','p.plan_teacher=u.user_id','LEFT')
                ->join('up_plan pl','p.plan_p_id=pl.p_id','LEFT')
                ->where($where)
                ->order('plan_online asc,plan_status DESC')
                ->page($NowPage,$limits)
                ->field($field)
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $this->assign('user_id',$user_id);
        return $this->fetch();
    }
    /**
     * 练习记录
     * @return mixed|\think\response\Json
     */
    public function practice(){
        $user_id = input('user_id');
        if(request()->isAjax()){
            $where = [
                'pra_online' => ['<>',3],
                'pra_student' => $user_id
            ];
            $keyword = input('keyword');
            if(isset($keyword) && $keyword != ''){
                $where['pra_name'] = ['like', '%' . $keyword . '%'];
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $field = 'p.*,p_name';
            $count =  Db::table('up_plan_practice')->alias('p')
                ->join('up_plan pl','p.pra_p_id=pl.p_id','LEFT')
                ->where($where)
                ->count();// 获取总条数
            $lists =  Db::table('up_plan_practice')->alias('p')
                ->join('up_plan pl','p.pra_p_id=pl.p_id','LEFT')
                ->where($where)
                ->order('pra_online asc,pra_status DESC')
                ->page($NowPage,$limits)
                ->field($field)
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $this->assign('user_id',$user_id);
        return $this->fetch();
    }
    public function practice_info(){
        $pra_id = input('pra_id');
        $data = Db::table('up_plan_practice')->where('pra_id', $pra_id)->find();
        $this->assign('data',$data);
        return $this->fetch();
    }
    /**
     * 导出
     */
    public function daochu()
    {
        set_time_limit(0);
        $where['user_status'] = ['neq', 3];
        $keyword = input('keyword');
        $start = input('res_start');
        $end = input('res_end');
        $start2 = input('login_start');
        $end2 = input('login_end');
        $user_school = input('user_school');
        $sex = input('sex');
        $utype = input('utype');
        $age = input('age');
        if(isset($keyword) && $keyword != ''){
            $where['user_id|user_username|user_phone'] = ['like', '%' . $keyword . '%'];
        }

        if(isset($start) && $start != "" && isset($end) && $end == "")
        {
            $where['user_time'] = ['>=',$start];
        }
        if(isset($end) && $end != "" && isset($start) && $start == "")
        {
            $where['user_time'] = ['<=',$end];
        }
        if(isset($start) && $start != "" && isset($end) && $end != "")
        {
            $where['user_time'] = ['between',[$start,$end]];
        }

        if(isset($start2) && $start2 != "" && isset($end2) && $end2 == "")
        {
            $where['user_last_login_time'] = ['>=',$start2];
        }
        if(isset($end2) && $end2 != "" && isset($start2) && $start2 == "")
        {
            $where['user_last_login_time'] = ['<=',$end2];
        }
        if(isset($start2) && $start2 != "" && isset($end2) && $end2 != "")
        {
            $where['user_last_login_time'] = ['between',[$start2,$end2]];
        }
        //琴行
        if(isset($user_school) && $user_school != "")
        {
            $where['user_piano'] = ['like','%'.$user_school.'%'];
        }
        //性别
        if(isset($sex) && $sex != "")
        {
            $where['user_gender'] = $sex;
        }
        //身份
        if(isset($utype) && $utype != "")
        {
            $where['user_type'] = $utype;
        }
        //年龄
        if(isset($age) && $age != "" && (int)$age > 1)
        {
            $age = (int)$age;
            $now = date('Y') - $age;
            $agedate1 = $now . '-01-01';
            $agedate2 = $now . '-12-31';
            $where['user_birthday'] = ['between',[$agedate1,$agedate2]];;
        }
        $field = [
            'user_id','user_username','user_phone','user_gender','user_birthday',
            'user_status','user_password axes','user_password panios','user_time','user_last_login_time',
            'user_type','user_like_axe','user_piano'
        ];
        $lists = Db::name('u_user')
            ->where($where)
            ->field($field)
            ->order('user_time desc')
            ->select();
        foreach ($lists as $key => $vo){
            $lists[$key]['axes'] = '';
            $lists[$key]['panios'] = '';
            $yueqi = Db::table('up_axe')->where('axe_id','in',trim($vo['user_like_axe'],','))->column('axe_name');
            if($yueqi){
                $lists[$key]['axes'] = implode($yueqi,',');
            }
            $panios = Db::table('up_piano')->where('pa_id','in',trim($vo['user_piano'],','))->column('pa_name');
            if($panios){
                $lists[$key]['panios'] = implode($panios,',');
            }
            unset($lists[$key]['user_like_axe']);
            unset($lists[$key]['user_piano']);
            //

            if($lists[$key]['user_gender'] == 1){
                $lists[$key]['user_gender'] = '男';
            }else{
                $lists[$key]['user_gender'] = '女';
            }
            //

            if($lists[$key]['user_status'] == 2){
                $lists[$key]['user_status'] = '冻结';
            }else{
                $lists[$key]['user_status'] = '正常';
            }
            //

            if($lists[$key]['user_type'] == 2){
                $lists[$key]['user_type'] = '老师';
            }else{
                $lists[$key]['user_type'] = '学生';
            }

        }
        $cellname = [
            'ID', '昵称', '登录手机', '性别', '生日',
            '状态','喜爱的乐器', '所属琴行', '注册时间', '最后登录时间',
            '用户类型'
        ];
        $res = exportExcel('userinfo',time(),$cellname,$lists);
        return json($res);
    }
}